Amazon AthenaでINSERT INTOクエリが”HIVE_UNSUPPORTED_FORMAT”エラーとなる際の対処

こんにちは、CX事業本部 IoT事業部の若槻です。

今回は、Amazon AthenaでのINSERT INTOクエリの実行でHIVE_UNSUPPORTED_FORMATエラーとなる際の対処についてです。

事象

次のCREATE EXTERNAL TABLEクエリにより外部テーブルcountsを作成しました。

CREATE EXTERNAL TABLE counts(
  id string,
  count int
)
ROW FORMAT
  SERDE
    'org.openx.data.jsonserde.JsonSerDe'
STORED AS
  INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
  OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://cm-test-20220730/counts'
TBLPROPERTIES ('has_encrypted_data'='false')

作成したテーブルに対してINSERT INTOクエリによりレコードを作成します。

INSERT INTO counts (id, count)
VALUES ('u001', 100), ('u002', 50), ('u001', 200)

しかしクエリ実行は次のようなエラーとなってしまいました。

HIVE_UNSUPPORTED_FORMAT: Output format org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat with SerDe org.openx.data.jsonserde.JsonSerDe is not supported. If a data manifest file was generated at 's3://aws-sam-cli-managed-default-samclisourcebucket-c376z02dmoa7/athena-query-result-primary-wg/Unsaved/2022/07/30/0b428d84-9f63-4bdc-b3c1-0347a1f9300a-manifest.csv', you may need to manually clean the data from locations specified in the manifest. Athena will not delete data in your account.

原因、対処

原因は、OpenX JSON SerDe（org.openx.data.jsonserde.JsonSerDe）がINSERT INTOで非対応のSerDeのためでした。

Supported formats and SerDesを確認すると、JSON Formatの場合のSerDeはHive JSON SerDe（org.apache.hive.hcatalog.data.JsonSerDe）のみ対応しているとあります。

そこで、SerDeを修正した外部テーブルcounts2を作成します。

CREATE EXTERNAL TABLE counts2(
  id string,
  count int
)
ROW FORMAT
  SERDE
    'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS
  INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
  OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://cm-test-20220730/counts'
TBLPROPERTIES ('has_encrypted_data'='false')

そしてINSERT INTOクエリを実行すると、今度は成功しました。

INSERT INTO counts2 (id, count)
VALUES ('u001', 100), ('u002', 50), ('u001', 200)

SELECTクエリによりINSERTされたレコードが取得できました。

2つのSerDeの違い

Hive JSON SerDeは、JSONデータを処理する際に一般的に使われるSerDeです。レコードの区切りは改行コード（\n）が使われます。

一方でOpenX JSON SerDeは、Hive JSON SerDeに加えてignore.malformed.jsonやcase.insensitiveなどのプロパティが使用できます。

よって、データの取得はOpenX JSON SerDe、INSERTはHive JSON SerDeのテーブルをそれぞれ用意するという手もあるかと思います。

以上